﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using UniMana.Data;
using System.Text;

namespace UniMana.Data
{
    public static class ReportTraining_Class_StudentListData_Chi
    {
        public static IQueryable Training_Class_StudentList(this Table<Training_Class>traClass, 
                                                     Table<Catalogue_Course> cataCourse,
                                                     Table<Catalogue_TrainingType>cataTraType,
                                                     Table<Catalogue_Department>cataDep,
                                                     Table<Training_Student_Class> student_Class,
                                                     Table<Catalogue_Level>cataLevel,
                                                     Table<Personnel_Personnel>pePer,
                                                     Table<Training_Student> student, int _iCourse, int _iDep, int _iLevel, int _iCataTraType)
        {
            var view1 = from t in traClass
                        join t0 in student_Class on new { Training_ClassKey = t.Training_ClassKey } equals new { Training_ClassKey = Convert.ToInt32(t0.Training_ClassKey) }
                        join t1 in cataTraType
                              on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t0.Catalogue_TrainingTypeKey), Catalogue_TrainingType = Convert.ToInt32(t.Catalogue_TrainingType) }
                          equals new { Catalogue_TrainingTypeKey = t1.Catalogue_TrainingTypeKey, Catalogue_TrainingType = t1.Catalogue_TrainingTypeKey }
                        join t2 in cataLevel
                              on new { Catalogue_LevelKey = Convert.ToInt32(t0.Catalogue_LevelKey), Column1 = Convert.ToInt32(t.Catalogue_LevelKey) }
                          equals new { Catalogue_LevelKey = t2.Catalogue_LevelKey, Column1 = t2.Catalogue_LevelKey }
                        join t3 in cataCourse
                              on new { Catalogue_CourseKey = Convert.ToInt32(t0.Catalogue_CourseKey), Column1 = Convert.ToInt32(t.Catalogue_CourseKey) }
                          equals new { Catalogue_CourseKey = t3.Catalogue_CourseKey, Column1 = t3.Catalogue_CourseKey }
                        join t4 in cataDep on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t4.Catalogue_DepartmentKey }
                        join t5 in pePer on t.Personnel_PersonnelKey equals t5.Personnel_PersonnelKey
                        join t6 in student on t0.Training_StudentKey equals t6.Training_StudentKey
                        group new { t3, t2, t1, t, t4, t5 } by new
                        {
                            t3.Catalogue_CourseKey,
                            t3.Name,
                            t2.Catalogue_LevelKey,
                            Column1 = t2.Name,
                            t1.Catalogue_TrainingTypeKey,
                            Column2 = t1.Name,
                            t.Training_ClassKey,
                            t.ID,
                            Column3 = t.Name,
                            t4.Catalogue_DepartmentKey,
                            Column4 = t4.Name,
                            t5.Personnel_PersonnelKey,
                            Column5 = t5.Name,
                            t5.LastName
                        } into g
                        where g.Key.Catalogue_DepartmentKey == _iDep &&
                          g.Key.Catalogue_CourseKey == _iCourse &&
                          g.Key.Catalogue_LevelKey == _iLevel &&
                          g.Key.Catalogue_TrainingTypeKey == _iCataTraType
                        select new
                        {
                            KeyKhoaHoc = (System.Int32?)g.Key.Catalogue_CourseKey,
                            TenKhoaHoc = g.Key.Name,
                            MaBacDaoTao = (System.Int32?)g.Key.Catalogue_LevelKey,
                            TenBacDaoTao = g.Key.Column1,
                            KeyLHDT = (System.Int32?)g.Key.Catalogue_TrainingTypeKey,
                            TenLHDT = g.Key.Column2,
                            KeyLop = (System.Int32?)g.Key.Training_ClassKey,
                            MaLop = g.Key.ID,
                            TenLop = g.Key.Column3,
                            KeyKhoa = (System.Int32?)g.Key.Catalogue_DepartmentKey,
                            TenKhoa = g.Key.Column4,
                            KeyGVCN = g.Key.Personnel_PersonnelKey,
                            HoGVCN = g.Key.LastName,
                            TenGVCN = g.Key.Column5,
                            TongSoSV = (Int64?)g.Count()
                        };
            return view1;

        }
    }
}
